package com.tbsoft.ehr.dao.overtime;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.StaffInfo;
import com.tbsoft.ehr.model.WorkOvertime;

public class OvertimeDaoImpl extends BaseDaoImpl<WorkOvertime> implements
		OvertimeDao {

	@Override
	public WorkOvertime getOvertime(final WorkOvertime overtime) {
		List<WorkOvertime> lst = getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("from WorkOvertime ot where ot.staff = ? and ot.yearMonth=?");
				q.setString(0, overtime.getStaff().getStaffId());
				q.setString(1, overtime.getYearMonth());
				return q.list();
			}
		});
		if(lst.size()>0){
			return lst.get(0);
		}
		return null;
	}

	@Override
	public List<WorkOvertime> getOvertimesByStaffId(final String staffId) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query q = session.createQuery("from WorkOvertime ot where ot.staff = ? and ot.pay='N' ");
				q.setString(0, staffId);
				return q.list();
			}
		});
	}

	@Override
	public List<WorkOvertime> getOvertimeByCondton(List<StaffInfo> staffLst,
			final String yearMonth, final int startIndex,final int count) {
		StringBuilder sb = new StringBuilder("from WorkOvertime ot where ot.status<>'D' ");
		StringBuilder sb2 = new StringBuilder();
		if(staffLst!=null&&staffLst.size()>0) {
			sb2.append("and ot.staff.userId in(");
			for(StaffInfo si:staffLst){
				sb2.append("'").append(si.getUserId()).append("',");
			}
			int i = sb2.lastIndexOf(",");
			sb2.replace(i, i+1, ") ");
		}
		sb.append(sb2.toString());
		if (yearMonth != null &&!yearMonth.isEmpty()) {
			sb.append("and ot.yearMonth= '").append(yearMonth).append("' ");
		}
		sb.append(" order by ot.staff,ot.yearMonth ");
		final String queryStr = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q= session.createQuery(queryStr);
				return q.setFirstResult(startIndex).setMaxResults(count).list();
			}
		});
	}

	@Override
	public int getOvertimeCount(List<StaffInfo> staffLst,final String yearMonth) {
		StringBuilder sb = new StringBuilder("select count(*) from WorkOvertime ot where ot.status<>'D' ");
		StringBuilder sb2 = new StringBuilder();
		if(staffLst!=null&&staffLst.size()>0) {
			sb2.append("and ot.staff.userId in(");
			for(StaffInfo si:staffLst){
				sb2.append("'").append(si.getUserId()).append("',");
			}
			int i = sb2.lastIndexOf(",");
			sb2.replace(i, i+1, ") ");
		}
		sb.append(sb2.toString());
		if (yearMonth != null &&!yearMonth.isEmpty()) {
			sb.append("and ot.yearMonth= '").append(yearMonth).append("' ");
		}
		final String queryStr = sb.toString();
		
		Long count = (Long)getHibernateTemplate().execute(new HibernateCallback() {			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q= session.createQuery(queryStr);
				return q.uniqueResult();
			}
		});
		return count.intValue();
	}

	@Override
	public WorkOvertime getOvertimeById(int id) {
		return (WorkOvertime)getHibernateTemplate().get(WorkOvertime.class, id);
	}	

}
